R Notebook

#install.packages("knitr")

Warning message:
In (function (file, envir = parent.frame(), verbose = FALSE)  :
  strings not representable in native encoding will be translated to UTF-8

#install.packages("grid")
#install.packages("MLRMPA")
#install.packages("dprep")
#install.packages("normalr")
#install.packages("ggcorrplot")
#install.packages("RColorBrewer")
#install.packages("rgdal")
#install.packages("jsonlite")
#install.packages("RColorBrewer")
#install.packages("readr")
Sys.setlocale("LC_ALL", "English")

[1] "LC_COLLATE=English_United States.1252;LC_CTYPE=English_United States.1252;LC_MONETARY=English_United States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252"

Sys.setenv("LANGUAGE"="En")

library(gridExtra)
library(dplyr)

Attaching package: 㤼㸱dplyr㤼㸲

The following object is masked from 㤼㸱package:gridExtra㤼㸲:

    combine

The following objects are masked from 㤼㸱package:stats㤼㸲:

    filter, lag

The following objects are masked from 㤼㸱package:base㤼㸲:

    intersect, setdiff, setequal, union

library(lubridate)

Attaching package: 㤼㸱lubridate㤼㸲

The following objects are masked from 㤼㸱package:base㤼㸲:

    date, intersect, setdiff, union

library(magrittr)
library(ggplot2)
library(tidyr)

Attaching package: 㤼㸱tidyr㤼㸲

The following object is masked from 㤼㸱package:magrittr㤼㸲:

    extract

library(knitr)
library(normalr)
library(ggcorrplot)
library(leaflet)
library(plotly)

Attaching package: 㤼㸱plotly㤼㸲

The following object is masked from 㤼㸱package:ggplot2㤼㸲:

    last_plot

The following object is masked from 㤼㸱package:stats㤼㸲:

    filter

The following object is masked from 㤼㸱package:graphics㤼㸲:

    layout

library(RColorBrewer)
library(readr)

#??src_mysql
my_db <- src_mysql(
  dbname = "covid19",
  host = "cvd-database.cmxpnyylhkiw.us-east-2.rds.amazonaws.com",
  user = "admin",
  password = "RprojectCovid19"
)

`src_mysql()` was deprecated in dplyr 1.0.0.
Please use `tbl()` directly with a database connection

my_db

src:  mysql 8.0.21 [@cvd-database.cmxpnyylhkiw.us-east-2.rds.amazonaws.com:/covid19]
tbls: covid19_confirmed, covid19_confirmed_global, covid19_deaths, covid19_deaths_global, covid19_recovered,
  covid19_recovered_global, covid19_thailand, covidus, data_ethnic, data_gender, data_health, data_lockdown,
  data_population, data_testing, gdp19, healthranking, population, pornhub, sars_2003, us_gdp, us_homeless, us_latlong,
  world_temp

##import data
df_conf <- tbl(my_db, sql("select * from covid19_confirmed"))
df_conf <- as.data.frame(df_conf)
df_conf

df_deaths <- tbl(my_db, sql("select * from covid19_deaths"))
df_deaths <- as.data.frame(df_deaths)
df_deaths

df_recover <- tbl(my_db, sql("select * from covid19_recovered"))
df_recover <- as.data.frame(df_recover)
df_recover

df_conf)[5:n.col]

Error: unexpected ')' in "df_conf)"

#clean data
cleanData <- function(data) {
  ## remove some columns
  data %<>% select(-c(Province.State, Lat, Long)) %>% rename(country=Country.Region)
  ## convert from wide to long format
  data %<>% gather(key=date, value=count, -country)
  ## convert from character to date
  data %<>% mutate(date = date %>% mdy())
  ## aggregate by country
  data %<>% group_by(country, date) %>% summarise(count=sum(count, na.rm=T)) %>% as.data.frame()
  return(data)
}
## clean the three data sets
data.confirmed <- df_conf %>% cleanData() %>% rename(confirmed=count)

`summarise()` has grouped output by 'country'. You can override using the `.groups` argument.

data.deaths <- df_deaths %>% cleanData() %>% rename(deaths=count)

`summarise()` has grouped output by 'country'. You can override using the `.groups` argument.

data.recovered <- df_recover %>% cleanData() %>% rename(recovered=count)

`summarise()` has grouped output by 'country'. You can override using the `.groups` argument.

data <- data.confirmed %>% merge(data.deaths, all=T) %>% merge(data.recovered, all=T)
data

## countries/regions with confirmed cases, excl. cruise ships
countries <- data %>% pull(country) %>% setdiff('Cruise Ship')
data

data.world <- data %>% group_by(date) %>%
  summarise(country='World',
            confirmed = sum(confirmed, na.rm=T),
            deaths = sum(deaths, na.rm=T),
            recovered = sum(recovered, na.rm=T))
data %<>% rbind(data.world)
data

data %<>% mutate(current.confirmed = confirmed - deaths - recovered)
#View(data)

world.location [, ncol(world.location )]

  [1] "Afghanistan - : 54483"                               "Albania - : 70655"                                  
  [3] "Algeria - : 105124"                                  "Andorra - : 9416"                                   
  [5] "Angola - : 19269"                                    "Antigua and Barbuda - : 195"                        
  [7] "Argentina - : 1853830"                               "Armenia - : 165711"                                 
  [9] "Australia - Australian Capital Territory: 118"       "Australia - New South Wales: 5084"                  
 [11] "Australia - Northern Territory: 98"                  "Australia - Queensland: 1303"                       
 [13] "Australia - South Australia: 596"                    "Australia - Tasmania: 234"                          
 [15] "Australia - Victoria: 20437"                         "Australia - Western Australia: 890"                 
 [17] "Austria - : 401886"                                  "Azerbaijan - : 228526"                              
 [19] "Bahamas - : 8101"                                    "Bahrain - : 99210"                                  
 [21] "Bangladesh - : 530890"                               "Barbados - : 1156"                                  
 [23] "Belarus - : 234111"                                  "Belgium - : 689271"                                 
 [25] "Belize - : 11700"                                    "Benin - : 3643"                                     
 [27] "Bhutan - : 851"                                      "Bolivia - : 198257"                                 
 [29] "Bosnia and Herzegovina - : 119420"                   "Botswana - : 19654"                                 
 [31] "Brazil - : 8753920"                                  "Brunei - : 175"                                     
 [33] "Bulgaria - : 214430"                                 "Burkina Faso - : 9857"                              
 [35] "Burma - : 136591"                                    "Burundi - : 1380"                                   
 [37] "Cabo Verde - : 13381"                                "Cambodia - : 456"                                   
 [39] "Cameroon - : 28010"                                  "Canada - Alberta: 119757"                           
 [41] "Canada - British Columbia: 63484"                    "Canada - Diamond Princess: 0"                       
 [43] "Canada - Grand Princess: 13"                         "Canada - Manitoba: 28260"                           
 [45] "Canada - New Brunswick: 1087"                        "Canada - Newfoundland and Labrador: 398"            
 [47] "Canada - Northwest Territories: 31"                  "Canada - Nova Scotia: 1570"                         
 [49] "Canada - Nunavut: 267"                               "Canada - Ontario: 255074"                           
 [51] "Canada - Prince Edward Island: 110"                  "Canada - Quebec: 250491"                            
 [53] "Canada - Saskatchewan: 21643"                        "Canada - Yukon: 70"                                 
 [55] "Central African Republic - : 4974"                   "Chad - : 3104"                                      
 [57] "Chile - : 690066"                                    "China - Anhui: 994"                                 
 [59] "China - Beijing: 1023"                               "China - Chongqing: 591"                             
 [61] "China - Fujian: 534"                                 "China - Gansu: 182"                                 
 [63] "China - Guangdong: 2099"                             "China - Guangxi: 267"                               
 [65] "China - Guizhou: 147"                                "China - Hainan: 171"                                
 [67] "China - Hebei: 1264"                                 "China - Heilongjiang: 1330"                         
 [69] "China - Henan: 1303"                                 "China - Hong Kong: 9928"                            
 [71] "China - Hubei: 68150"                                "China - Hunan: 1027"                                
 [73] "China - Inner Mongolia: 366"                         "China - Jiangsu: 690"                               
 [75] "China - Jiangxi: 935"                                "China - Jilin: 351"                                 
 [77] "China - Liaoning: 396"                               "China - Macau: 47"                                  
 [79] "China - Ningxia: 75"                                 "China - Qinghai: 18"                                
 [81] "China - Shaanxi: 533"                                "China - Shandong: 866"                              
 [83] "China - Shanghai: 1635"                              "China - Shanxi: 235"                                
 [85] "China - Sichuan: 866"                                "China - Tianjin: 335"                               
 [87] "China - Tibet: 1"                                    "China - Xinjiang: 980"                              
 [89] "China - Yunnan: 231"                                 "China - Zhejiang: 1316"                             
 [91] "Colombia - : 1987418"                                "Comoros - : 2160"                                   
 [93] "Congo (Brazzaville) - : 7794"                        "Congo (Kinshasa) - : 21597"                         
 [95] "Costa Rica - : 189308"                               "Cote d'Ivoire - : 26315"                            
 [97] "Croatia - : 227969"                                  "Cuba - : 20060"                                     
 [99] "Cyprus - : 29758"                                    "Czechia - : 924847"                                 
[101] "Denmark - Faroe Islands: 652"                        "Denmark - Greenland: 30"                            
[103] "Denmark - : 193038"                                  "Diamond Princess - : 712"                           
[105] "Djibouti - : 5917"                                   "Dominica - : 113"                                   
[107] "Dominican Republic - : 201145"                       "Ecuador - : 237158"                                 
[109] "Egypt - : 160463"                                    "El Salvador - : 52672"                              
[111] "Equatorial Guinea - : 5401"                          "Eritrea - : 1940"                                   
[113] "Estonia - : 39701"                                   "Eswatini - : 14219"                                 
[115] "Ethiopia - : 132881"                                 "Fiji - : 55"                                        
[117] "Finland - : 41915"                                   "France - French Guiana: 15431"                      
[119] "France - French Polynesia: 17852"                    "France - Guadeloupe: 8948"                          
[121] "France - Martinique: 6302"                           "France - Mayotte: 7158"                             
[123] "France - New Caledonia: 44"                          "France - Reunion: 9584"                             
[125] "France - Saint Barthelemy: 300"                      "France - Saint Pierre and Miquelon: 16"             
[127] "France - St Martin: 1146"                            "France - Wallis and Futuna: 4"                      
[129] "France - : 3002910"                                  "Gabon - : 10278"                                    
[131] "Gambia - : 3958"                                     "Georgia - : 251974"                                 
[133] "Germany - : 2125261"                                 "Ghana - : 59480"                                    
[135] "Greece - : 151041"                                   "Grenada - : 139"                                    
[137] "Guatemala - : 152956"                                "Guinea - : 14262"                                   
[139] "Guinea-Bissau - : 2510"                              "Guyana - : 7143"                                    
[141] "Haiti - : 10963"                                     "Holy See - : 27"                                    
[143] "Honduras - : 139182"                                 "Hungary - : 356973"                                 
[145] "Iceland - : 5981"                                    "India - : 10639684"                                 
[147] "Indonesia - : 965283"                                "Iran - : 1360852"                                   
[149] "Iraq - : 612092"                                     "Ireland - : 184279"                                 
[151] "Israel - : 589028"                                   "Italy - : 2441854"                                  
[153] "Jamaica - : 14658"                                   "Japan - : 357174"                                   
[155] "Jordan - : 318911"                                   "Kazakhstan - : 222701"                              
[157] "Kenya - : 99769"                                     "Korea, South - : 74692"                             
[159] "Kosovo - : 57317"                                    "Kuwait - : 160367"                                  
[161] "Kyrgyzstan - : 83796"                                "Laos - : 41"                                        
[163] "Latvia - : 59586"                                    "Lebanon - : 272461"                                 
[165] "Lesotho - : 7555"                                    "Liberia - : 1912"                                   
[167] "Libya - : 112540"                                    "Liechtenstein - : 2432"                             
[169] "Lithuania - : 174922"                                "Luxembourg - : 49438"                               
[171] "MS Zaandam - : 9"                                    "Madagascar - : 18301"                               
[173] "Malawi - : 17365"                                    "Malaysia - : 176180"                                
[175] "Maldives - : 14830"                                  "Mali - : 7937"                                      
[177] "Malta - : 16423"                                     "Marshall Islands - : 4"                             
[179] "Mauritania - : 16322"                                "Mauritius - : 556"                                  
[181] "Mexico - : 1732290"                                  "Micronesia - : 1"                                   
[183] "Moldova - : 155302"                                  "Monaco - : 1311"                                    
[185] "Mongolia - : 1611"                                   "Montenegro - : 57482"                               
[187] "Morocco - : 464844"                                  "Mozambique - : 30848"                               
[189] "Namibia - : 31848"                                   "Nepal - : 268948"                                   
[191] "Netherlands - Aruba: 6656"                           "Netherlands - Bonaire, Sint Eustatius and Saba: 196"
[193] "Netherlands - Curacao: 4543"                         "Netherlands - Sint Maarten: 1724"                   
[195] "Netherlands - : 938628"                              "New Zealand - : 2276"                               
[197] "Nicaragua - : 6204"                                  "Niger - : 4308"                                     
[199] "Nigeria - : 118138"                                  "North Macedonia - : 90124"                          
[201] "Norway - : 60565"                                    "Oman - : 132486"                                    
[203] "Pakistan - : 530818"                                 "Panama - : 307793"                                  
[205] "Papua New Guinea - : 835"                            "Paraguay - : 126370"                                
[207] "Peru - : 1082907"                                    "Philippines - : 509887"                             
[209] "Poland - : 1464448"                                  "Portugal - : 609136"                                
[211] "Qatar - : 148521"                                    "Romania - : 706475"                                 
[213] "Russia - : 3637862"                                  "Rwanda - : 12443"                                   
[215] "Saint Kitts and Nevis - : 35"                        "Saint Lucia - : 755"                                
[217] "Saint Vincent and the Grenadines - : 690"            "Samoa - : 2"                                        
[219] "San Marino - : 2861"                                 "Sao Tome and Principe - : 1182"                     
[221] "Saudi Arabia - : 365988"                             "Senegal - : 24209"                                  
[223] "Serbia - : 380802"                                   "Seychelles - : 972"                                 
[225] "Sierra Leone - : 3093"                               "Singapore - : 59250"                                
[227] "Slovakia - : 233027"                                 "Slovenia - : 155752"                                
[229] "Solomon Islands - : 17"                              "Somalia - : 4744"                                   
[231] "South Africa - : 1392568"                            "South Sudan - : 3788"                               
[233] "Spain - : 2499560"                                   "Sri Lanka - : 56863"                                
[235] "Sudan - : 28522"                                     "Suriname - : 7945"                                  
[237] "Sweden - : 547166"                                   "Switzerland - : 509279"                             
[239] "Syria - : 13479"                                     "Taiwan* - : 881"                                    
[241] "Tajikistan - : 13308"                                "Tanzania - : 509"                                   
[243] "Thailand - : 13104"                                  "Timor-Leste - : 53"                                 
[245] "Togo - : 4545"                                       "Trinidad and Tobago - : 7456"                       
[247] "Tunisia - : 193273"                                  "Turkey - : 2418472"                                 
[249] "US - : 24846678"                                     "Uganda - : 38935"                                   
[251] "Ukraine - : 1222459"                                 "United Arab Emirates - : 270810"                    
[253] "United Kingdom - Anguilla: 15"                       "United Kingdom - Bermuda: 684"                      
[255] "United Kingdom - British Virgin Islands: 114"        "United Kingdom - Cayman Islands: 382"               
[257] "United Kingdom - Channel Islands: 3414"              "United Kingdom - Falkland Islands (Malvinas): 35"   
[259] "United Kingdom - Gibraltar: 3905"                    "United Kingdom - Isle of Man: 432"                  
[261] "United Kingdom - Montserrat: 13"                     "United Kingdom - Turks and Caicos Islands: 1193"    
[263] "United Kingdom - : 3583907"                          "Uruguay - : 36170"                                  
[265] "Uzbekistan - : 78272"                                "Vanuatu - : 1"                                      
[267] "Venezuela - : 122795"                                "Vietnam - : 1548"                                   
[269] "West Bank and Gaza - : 154557"                       "Yemen - : 2118"                                     
[271] "Zambia - : 43333"                                    "Zimbabwe - : 30523"                                 

## China
map.confirmed %>% setView(95, 35, zoom=4)

## Australia and New Zealand
map.confirmed %>% setView(135, -27, zoom=4)

## US and Canada
map.confirmed %>% setView(-105, 40, zoom=4)

## Europe
map.confirmed %>% setView(10, 50, zoom=4)

# new.confirmed new.deaths new.recovered
data %<>% arrange(country, date)
n <- nrow(data)
day1 <- min(data$date)
data %<>% mutate(new.confirmed = ifelse(date == day1, NA, confirmed - lag(confirmed, n=1)),
                 new.deaths = ifelse(date == day1, NA, deaths - lag(deaths, n=1)),
                 new.recovered = ifelse(date == day1, NA, recovered - lag(recovered, n=1)))
data %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths),
                 new.recovered = ifelse(new.recovered < 0, 0, new.recovered))
## death rate based on total deaths and recovered cases
data %<>% mutate(rate.upper = (100 * deaths / (deaths + recovered)) %>% round(1),
                 rate.upper = ifelse(is.nan(rate.upper), 0, rate.upper))

## lower bound: death rate based on total confirmed cases
data %<>% mutate(rate.lower = (100 * deaths / confirmed) %>% round(1),
                 rate.lower = ifelse(is.nan(rate.lower), 0, rate.lower))

## death rate based on the number of death/recovered on every single day
data %<>% mutate(rate.daily = (100 * new.deaths / (new.deaths + new.recovered)) %>% round(1),
                 rate.daily = ifelse(is.nan(rate.daily), 0, rate.daily))

#View(data)

## convert from wide to long format
data.long <- data %>%
  select(c(country, date, confirmed, current.confirmed, recovered, deaths)) %>%
  gather(key=type, value=count, -c(country, date))
## set factor levels to show them in a desirable order
data.long %<>% mutate(type=recode_factor(type, confirmed='Total Confirmed',
                                         current.confirmed='Current Confirmed',
                                         recovered='Recovered',
                                         deaths='Deaths'))
#View(data.long)

##Number of case World
world <- filter(data.long,country == 'World')
world

plot_world.area <- world %>% filter(type != 'Total Confirmed') %>%
  ggplot(aes(x=date, y=count)) +
  geom_area(aes(fill=type), alpha=0.5) +
  labs(title=paste0('Numbers of Cases Worldwide - ', max.date.txt)) +
  scale_fill_manual(values=c('red', 'green', 'black')) +
  theme(legend.title=element_blank(), legend.position='bottom',
        plot.title = element_text(size=7),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        legend.key.size=unit(0.2, 'cm'),
        legend.text=element_text(size=6),
        axis.text=element_text(size=7),
        axis.text.x=element_text(angle=45, hjust=1))
plot_world.line <- world %>%
  ggplot(aes(x=date, y=count,color = type)) +
  geom_line(aes(color=type)) +
  labs(title=paste0('Numbers of Cases Worldwide (log scale) - ', max.date.txt)) +
  #scale_color_manual(values=c('blue', 'red', 'green', 'black')) +
  theme(legend.title=element_blank(), legend.position='bottom',
        plot.title = element_text(size=7),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        legend.key.size=unit(0.2, 'cm'),
        legend.text=element_text(size=6),
        axis.text=element_text(size=7),
        axis.text.x=element_text(angle=45, hjust=1)) +
  scale_y_continuous(trans='log10')
## show two plots side by side
grid.arrange(plot_world.area, plot_world.line, ncol=2)

gly.plot_world.area <- ggplotly(plot_world.area)
gly.plot_world.area

gly.plot_world.line <- ggplotly(plot_world.line)
gly.plot_world.line

data.world.all <- data %>% filter(country == "World")
## a scatter plot with a smoothed line and vertical x-axis labels
plot_acc.deaths <- ggplot(data.world.all, aes(x=date, y=deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot_recovered <- ggplot(data.world.all, aes(x=date, y=recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot_new.deaths <- ggplot(data.world.all, aes(x=date, y=new.deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot_new.recovered <- ggplot(data.world.all, aes(x=date, y=new.recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show four plots together, with 2 plots in each row
grid.arrange(plot_acc.deaths, plot_recovered, plot_new.deaths, plot_new.recovered, nrow=2)

## convert from wide to long format, for drawing area plots
rates.long <- data %>%
  select(c(country, date, rate.upper, rate.lower, rate.daily)) %>%
  gather(key=type, value=count, -c(country, date))
# set factor levels to show them in a desirable order
rates.long %<>% mutate(type=recode_factor(type, rate.daily='Daily',
                             rate.upper='Upper bound',
                             rate.lower = 'Lower bound')) 
#View(rates.long) 

plot_rates <- rates.long %>% filter(country == "World") %>% 
  ggplot(aes(x = date, y = count, color = type)) + 
  geom_line() +
  labs(title = "World Death Rate (%)") +
  xlab("") + ylab("Death Rate (%)")

plot_rates

gly.rates <- ggplotly(plot_rates)
gly.rates

## ranking by confirmed cases
data.latest.all <- data %>% filter(date == max(date)) %>%
  select(country, date,confirmed, new.confirmed, current.confirmed,
         recovered, deaths, new.deaths, death.rate=rate.lower) %>%
  mutate(ranking = dense_rank(desc(confirmed))) %>%
  arrange(ranking)
#View(data.latest.all)

k <- 20
## top 20 countries: 21 incl. 'World'
top.countries <- data.latest.all %>% filter(ranking <= k + 1) %>%
  arrange(ranking) %>% pull(country) %>% as.character()
top.countries %>% setdiff('World') %>% print()

 [1] "US"             "India"          "Brazil"         "Russia"         "United Kingdom" "France"         "Spain"         
 [8] "Italy"          "Turkey"         "Germany"        "Colombia"       "Argentina"      "Mexico"         "Poland"        
[15] "South Africa"   "Iran"           "Ukraine"        "Peru"           "Indonesia"      "Netherlands"   

df <- data.long %>% filter(country %in% top.countries) %>%
  mutate(country=country %>% factor(levels=c(top.countries))) %>% 
  filter(country != 'World' & type != 'Total Confirmed') %>%
  ggplot(aes(x=date, y=count, color=type)) +
  geom_line()+
  #geom_area(alpha=0.5) +
# xlab('') + ylab('') +
  labs(title=paste0('Numbers of COVID-19 Cases in Top 20 Countries - ',
                    max.date.txt)) +
  scale_color_manual(values=c('red', 'green', 'black')) +
  theme(legend.title=element_blank(), legend.position='bottom',
        plot.title = element_text(size=12),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        legend.key.size=unit(0.4, 'cm'),
        legend.text=element_text(size=12),
        strip.text.x=element_text(size=12),
        axis.text=element_text(size=12),
        axis.text.x=element_text(angle=45, hjust=1)) +
  facet_wrap(~country, ncol=4, scales='free_y') +
  scale_y_continuous(trans='log10')
df

data.latest <- data.latest.all %>% filter(!is.na(country)) %>%
  mutate(country=ifelse(ranking <= k + 1, as.character(country), 'Others')) %>%
  mutate(country=country %>% factor(levels=c(top.countries, 'Others')))
data.latest %<>% group_by(country) %>%
  summarise(confirmed=sum(confirmed), new.confirmed=sum(new.confirmed),
            current.confirmed=sum(current.confirmed),
            recovered=sum(recovered), deaths=sum(deaths), new.deaths=sum(new.deaths)) %>%
  mutate(death.rate=(100 * deaths/confirmed) %>% round(1)) 
data.latest

data.latest %<>% select(c(country, confirmed, deaths, death.rate,
                          new.confirmed, new.deaths, current.confirmed,recovered)) %>%
  mutate(recover.rate=(100 * recovered/confirmed) %>% round(1))
data.latest

mostconfirm <- data.latest %>% select(c(country,confirmed)) %>% filter(country=="World")
mostrecover <- data.latest %>% select(c(country,recovered)) %>% filter(country=="World")
mostdeath <- data.latest %>% select(c(country,deaths)) %>% filter(country=="World")


df_pop <- tbl(my_db, sql("select * from population "))
df_pop <- as.data.frame(df_pop)
df_pop <- rename(df_pop,"country"="Country")

# Add World Population
world_pop <- sum(df_pop$`Population (2020)`)
df_pop[nrow(df_pop) + 1,] = c("World", world_pop)

# Add Other Countries Population
top_pop <- filter(df_pop, df_pop$country %in% top.countries & df_pop$country != "World")
top_pop <- sum(top_pop$`Population (2020)` %>% as.numeric())
others_pop <- (world_pop - top_pop) 
df_pop[nrow(df_pop) + 1,] = c("Others", others_pop)
#View(df_pop)

data.latest <- merge(x = data.latest, y = df_pop, by = "country", all.x = TRUE) 
data.latest

data.latest <- rename(data.latest,"population" = "Population (2020)")
data.latest$population <- data.latest$population %>% as.numeric()
data.latest  <- data.latest %>%
  select(c(country, confirmed, deaths, death.rate,
                          new.confirmed, new.deaths,
                          current.confirmed, recovered, recover.rate, population)) %>%
  mutate(confirm.rate = (100 * confirmed / population) %>% round(1))
data.latest

## % of death
data.latest %>% mutate(death.rate=death.rate %>% format(nsmall=1) %>% paste0('%'))

## convert from wide to long format, for drawing area plots
data.latest.long <- data.latest %>% filter(country!='World') %>%
  gather(key=type, value=count, -country)
## set factor levels to show them with proper text and in a desirable order
data.latest.long %<>% mutate(type=recode_factor(type,
                                                confirmed='Total Confirmed',
                                                deaths='Total Deaths',
                                                death.rate='Death Rate (%)',
                                                new.confirmed='New Confirmed (compared with one day before)',
                                                new.deaths='New Deaths (compared with one day before)',
                                                current.confirmed='Current Confirmed',
                                                recover.rate = 'Recovered Rate(%)',
                                                confirm.rate = 'Confirmed Rate(%)'))
#View(data.latest.long)
data.one.dem <- filter(data.latest.long,type=='Total Confirmed'
                       | type=='Total Deaths'
                       | type=='Current Confirmed')
data.two.dem <- filter(data.latest.long,type=='Death Rate (%)'
                     #  | type=='New Confirmed (compared with one day before)'
                    #   | type=='New Deaths (compared with one day before)'
                       | type=='Recovered Rate(%)'
                       | type=='Confirmed Rate(%)')
data.two.dem

## bar chart
data.one.dem %>% ggplot(aes(x=country, y=count, fill=country, group=country)) +
  geom_bar(stat='identity') +
  geom_text(aes(label=count, y=count), size=3, vjust=0) +
  xlab('') + ylab('') +
  labs(title=paste0('Top 20 Countries with Most Confirmed Cases - ', max.date.txt)) +
  scale_fill_discrete(name='Country', labels=aes(count)) +
  theme(legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size=13),
        axis.text=element_text(size=8),
        axis.text.x=element_text(angle=45, hjust=1)) +
  facet_wrap(~type, ncol=1, scales='free_y')

data.two.dem$facet <- factor(data.two.dem$type, levels = c('Confirmed Rate(%)', 'Recovered Rate(%)','Death Rate (%)'))
data.two.dem %>% 
  ggplot(aes(x=country, y=count, fill=country, group=country)) +
  geom_bar(stat='identity') +
  geom_text(aes(label=count, y=count), size=4, vjust=0) +
  xlab('') + ylab('') +
  labs(title=paste0('Top 20 Countries with Most Confirmed Cases - ', max.date.txt)) +
  scale_fill_discrete(name='Country', labels=aes(count)) +
  theme(legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size=13),
        axis.text=element_text(size=10),
        axis.text.x=element_text(size=10,angle=45, hjust=1)) +
  facet_wrap(~facet, ncol=1, scales='free_y')

##GDP
df_gdp2019 <- tbl(my_db, sql("select * from gdp19"))
df_gdp2019 <- as.data.frame(df_gdp2019)
df_gdp2019

#healthranking data
df_healt <- tbl(my_db, sql("select * from healthranking"))
df_healt <- as.data.frame(df_healt)
df_healt <- select(df_healt,c("country","healthCareIndex"))
#View(df_healt)

#Top20Pornhub data
#df_pornhub <- tbl(my_db, sql("select * from Pornhub"))
#df_pornhub <- as.data.frame(df_pornhub)
#df_pornhub

# temp data
df_temp <- tbl(my_db, sql("select * from world_temp"))
df_temp <- as.data.frame(df_temp) 
df_temp$Country[df_temp$Country == "United States"] <- "US"

df_city <- select(df_temp,c("Country","City")) %>%
  rename(country=Country) %>% 
  rename(city=City)

numofcity <- aggregate(city ~ country, data = df_city, length)

#clean data
df_temp <- select(df_temp,c("Country","Avg_Year")) %>%
  rename(country=Country)
#View(df_temp)

#df_temp <- data.frame(country=df_temp[,1],avg=rowMeans(df_temp[,-1]))
df_temp <- df_temp %<>% group_by(country) %>% summarise(avg_temp = mean(Avg_Year,na.rm = TRUE)%>% round(1))
df_temp

##temp bar
df_temp.all <- df_temp %>% merge(data.latest.all)
#View(df_temp.all)
df_temp_top.all <- df_temp.all %>% filter(country %in% top.countries) %>%
  mutate(ranking = ranking - 1) %>%
  arrange(ranking)
#View(df_temp_top.all)
g_temp_top <- df_temp_top.all %>%
  ggplot(aes(x = reorder(country, ranking), y = avg_temp, fill = avg_temp)) +
  labs(title=paste0("Temperature in Top  20 countries"), subtitle = "Average Temperature in Top 20 countries with most confirmed cases (°C) (2020)") +
  scale_color_gradient(low = "#93DBFF", high = "#FF7771") +
  geom_text(aes(label=avg_temp, y=avg_temp), size=4, vjust=-0.5) +
  geom_bar(stat = "identity", position = "dodge") +
  theme(
        legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size = 15, hjust = 0.5),
        plot.subtitle = element_text(size = 12, hjust = 0.5),
        axis.text=element_text(size=8),
        axis.text.x=element_text(size = 9, angle=45, hjust=1)) +
  scale_x_discrete(name = "Country") +
  scale_y_discrete(name = "Average Temperature")

           #labs(title = "Temperature in Top  20 countries", subtitle = "Temperature in Top 20 countries with most confirmed cases (°C)")
         
#g_temp_top         
g_temp_top 

#data.latest.all
lat.long <- rename(df_conf, "country" = "Country.Region", "city" = "Province.State") %>% 
  select("country", "Lat", "Long") %>% 
  merge(df_temp.all[c("country","confirmed", "recovered", "deaths", "avg_temp", "ranking")], by = "country") %>%
  distinct(country, .keep_all = TRUE) %>%
  mutate(ranking = ranking - 1) %>%
  arrange(ranking)
#View(lat.long)

label_world <- lat.long 
label_world$avg_temp <- as.numeric(label_world[, names(label_world) %in% c("avg_temp")])
label_world <- label_world %>%  
  mutate(txt=paste0('<b>',ranking, '</b>',
                    '<br/>','<b>',country, '</b>',
                    '<br/>', "Temperature:  ",avg_temp, ' °C',
                    '<br/>', "Confirmed:  ", confirmed, 
                    '<br/>', "Deaths: ", deaths,
                    '<br/>', "Recovered: ", recovered
                    )) 

label_world$txt <- label_world$txt %>% lapply(htmltools::HTML)
label_world 

label_top <- label_world %>% filter(ranking < 21)
label_top

# Temperature Map
wpal <- colorNumeric("YlOrRd", label_world$avg_temp, n = 4)

topIcon <- makeIcon("star.png",
  #iconUrl = "https://static.vecteezy.com/system/resources/previews/001/189/063/non_2x/star-rounded-png.png",
  iconWidth = 10, iconHeight = 10
  #iconAnchorX = 20, iconAnchorY = 20
  
)

label_world <- label_world %>% filter(ranking > 20) 
  
m <- leaflet(width=1200, height=800) %>% addTiles()  
m %<>%  addCircleMarkers(label_world$Long, label_world$Lat,
                        # radius=2+log2(x$confirmed),
                        radius=10,#*log2(m.world$avg.temp),
                        stroke=F,
                        #color='red',
                        color = wpal(label_world$avg_temp), 
                        fillOpacity=0.5,
                        #popup=label.top$txt
                        label= label_world$txt,
                        group = "World"
                        ) %>%
  
  addCircleMarkers(label_top$Long, label_top$Lat,
                        # radius=2+log2(x$confirmed),
                        radius=10,#*log2(m.world$avg.temp),
                        stroke=F,
                        #color='red',
                        color = wpal(label_top$avg_temp), 
                        fillOpacity=0.5,
                        #popup=label.top$txt
                        label= label_top$txt,
                        group = "Top 20 Countries"
                        ) %>%
  
  addLabelOnlyMarkers(label_top$Long, label_top$Lat, label = label_top$ranking,
                      labelOptions = labelOptions(noHide = TRUE, textOnly = TRUE, 
                                                  direction = "head", 
                                                  offset = c(5,4)),
                      group = "Top 20 Countries") %>%
  
  addLegend("bottomright", pal = wpal, values = label_world$avg_temp, opacity = 1,
            labFormat = labelFormat(suffix = " °C"),
            title = "Temperature") %>% 
  
  addLayersControl(
    #baseGroups = c("OSM (default)", "Toner", "Toner Lite"),
    overlayGroups = c("Top 20 Countries", "World"),
    options = layersControlOptions(collapsed = FALSE)
  )
m

gdp.top20 <- df_gdp2019 %>%
  select(c("rank", "country", "GDP (millions of US dollars)")) %>%
  merge(data.latest.all %>% 
          select(country, ranking, confirmed, recovered, deaths) %>% 
          filter(country %in% top.countries & country != "World"), by = "country") %>%
  arrange(ranking) %>% 
  mutate(ranking = ranking - 1) 
gdp.top20 %<>% rename("GDP" = "GDP (millions of US dollars)")
gdp.top20

g <- ggplot(gdp.top20, aes(x = GDP, y = reorder(country, -ranking))) +
  geom_histogram(stat = "identity", aes(fill = GDP))+ 
  scale_fill_gradient("GDP", low = "#FF4038", high = "#50E952") + 
  labs(title=paste0("GDP  of Top 20 Countries in 2019 (millions of US dollars)")) +
  geom_text(aes(label=GDP, x = GDP), size=3.5, hjust=-0.2) +
  xlab("GDP (millions of US dollars)") +
  ylab("") +
  theme(legend.title=element_blank())

Ignoring unknown parameters: binwidth, bins, pad

g

gly.top.gdp <- ggplotly(g)
gly.top.gdp

# Pornhub
df_pornhub <- tbl(my_db, sql("select * from pornhub"))
df_pornhub <- as.data.frame(df_pornhub)
df_pornhub

# Sars
df_sars <- tbl(my_db, sql("select * from sars_2003"))
df_sars <- as.data.frame(df_sars)
#View(df_sars)

dates.s <- df_sars[,1]%>% mdy()
range(dates.s)

[1] "2003-03-17" "2003-07-11"

min.date.s <- min(dates.s)
max.date.s <- max(dates.s)
min.date.txt.s <- min.date.s %>% format('%d %b %Y')
max.date.txt.s <- max.date.s %>% format('%d %b %Y')
day1.sars <- min.date.s

# clean data Sars 
data.sars <- df_sars %>% rename(c("date" = "Date", "confirmed" = "Cumulative_number"  ,"deaths" = "Number_deaths", "recovered" = "Number_recovered")) %>%
  mutate(date = date %>% mdy()) %>%
  group_by(country, date) %>% as.data.frame() 

# Add World's Sars cases 
world.sars <- data.sars %>% group_by(date) %>% 
  summarise(country='World',
            confirmed = sum(confirmed, na.rm=T),
            deaths = sum(deaths, na.rm=T),
            recovered = sum(recovered, na.rm=T))

data.sars %<>% rbind(world.sars)
data.sars %<>% mutate(current.confirmed = confirmed - deaths - recovered)
#View(world.sars) 
data.sars

#rate
data.sars %<>% arrange(country, date)
n <- nrow(data.sars)
day1.sars <- min(data.sars$date)
data.sars %<>% mutate(new.confirmed = ifelse(date == day1.sars, NA, confirmed - lag(confirmed, n=1)),
                 new.deaths = ifelse(date == day1.sars, NA, deaths - lag(deaths, n=1)),
                 new.recovered = ifelse(date == day1.sars, NA, recovered - lag(recovered, n=1)))
data.sars %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths),
                 new.recovered = ifelse(new.recovered < 0, 0, new.recovered))
## death rate based on total deaths and recovered cases
data.sars %<>% mutate(rate.upper = (100 * deaths / (deaths + recovered)) %>% round(1),
                 rate.upper = ifelse(is.nan(rate.upper), 0, rate.upper))

## lower bound: death rate based on total confirmed cases
data.sars %<>% mutate(rate.lower = (100 * deaths / confirmed) %>% round(1),
                 rate.lower = ifelse(is.nan(rate.lower), 0, rate.lower))

## death rate based on the number of death/recovered on every single day
data.sars %<>% mutate(rate.daily = (100 * new.deaths / (new.deaths + new.recovered)) %>% round(1),
                 rate.daily = ifelse(is.nan(rate.daily), 0, rate.daily))

#View(data.sars)

## convert from wide to long format
data.sars.long <- data.sars %>%
  select(c(country, date, confirmed, current.confirmed, recovered, deaths)) %>%
  gather(key=type, value=count, -c(country, date))
## set factor levels to show them in a desirable order
data.sars.long %<>% mutate(type=recode_factor(type, confirmed='Total Confirmed',
                                         current.confirmed='Current Confirmed',
                                         recovered='Recovered',
                                         deaths='Deaths'))
#View(data.sars.long)

# World sars' long data 
world.sars.long <- data.sars.long %>%
  filter(country == "World")
#View(world.sars.long)

g <- ggplot(world.sars.long, aes(date, count, color = type)) +
  geom_line()+
  labs(title = "Number of Cases Worldwide: SARs")+
  xlab("")+
  ylab("")
g

gly.g <- ggplotly(g)
gly.g

gly.plot_world.line

df_sars_lastdate <- data.sars %>%
  filter(date == max.date.s)

df_sars_lastdate

## Current Confirmed Cases
data.sars.world <- data.sars %>% filter(country=='World')
#View(data.sars.world)
n <- nrow(data.sars.world)
#View(data.sars.world)
plot_sars.currconf <- ggplot(data.sars.world, aes(x=date, y=current.confirmed)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Current Confirmed Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot_sars.newconf <- ggplot(data.sars.world, aes(x=date, y=new.confirmed)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Daily New Confirmed Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show two plots side by side
grid.arrange(plot_sars.currconf, plot_sars.newconf, ncol=2)

## a scatter plot with a smoothed line and vertical x-axis labels
plot_sars.accdeaths <- ggplot(data.sars.world, aes(x=date, y=deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot_sars.accrecov <- ggplot(data.sars.world, aes(x=date, y=recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot_sars.newdeaths <- ggplot(data.sars.world, aes(x=date, y=new.deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot_sars.newrecov <- ggplot(data.sars.world, aes(x=date, y=new.recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show four plots together, with 2 plots in each row
grid.arrange(plot_sars.accdeaths, plot_sars.accrecov, plot_sars.newdeaths, plot_sars.newrecov, nrow=2)

#correlation
corr_data %<>% select(c(GDP,confirm.rate,death.rate,recover.rate,healthcare,avg_temp,Pornhub, sars))
head(corr_data)

cor(corr_data)

                     GDP confirm.rate  death.rate recover.rate  healthcare    avg_temp     Pornhub        sars
GDP           1.00000000    0.4342894 -0.23113988   -0.3937431  0.25183938 -0.05173909  0.71560225  0.97481805
confirm.rate  0.43428937    1.0000000 -0.34422360   -0.7182585  0.48153104 -0.36431596  0.43162144  0.45066089
death.rate   -0.23113988   -0.3442236  1.00000000    0.1733012 -0.17258588  0.34643395 -0.04353792 -0.21325881
recover.rate -0.39374308   -0.7182585  0.17330123    1.0000000 -0.76240251  0.26074521 -0.40887125 -0.35828282
healthcare    0.25183938    0.4815310 -0.17258588   -0.7624025  1.00000000 -0.06716339  0.37579971  0.16579089
avg_temp     -0.05173909   -0.3643160  0.34643395    0.2607452 -0.06716339  1.00000000 -0.11100774 -0.05509975
Pornhub       0.71560225    0.4316214 -0.04353792   -0.4088713  0.37579971 -0.11100774  1.00000000  0.68006901
sars          0.97481805    0.4506609 -0.21325881   -0.3582828  0.16579089 -0.05509975  0.68006901  1.00000000

plotcorr <- ggcorrplot(cor(corr_data),hc.order = TRUE,
           outline.color = "white",
           colors = c("#6D9EC1","white","#E46726"),
           lab = TRUE)

plotcorr

## Data in US

df_us <- tbl(my_db, sql("select * from covidus"))
df_us <- as.data.frame(df_us) 
df_us <- select(df_us, date, state, cases, deaths)

df_us_pop <- tbl(my_db, sql("select * from data_population")) 
df_us_pop <- as.data.frame(df_us_pop)

df_us_gender <- tbl(my_db, sql("select * from data_gender")) 
df_us_gender <- as.data.frame(df_us_gender)

df_us_ethnic <- tbl(my_db, sql("select * from data_ethnic")) 
df_us_ethnic <- as.data.frame(df_us_ethnic)

df_us_lockdown <- tbl(my_db, sql("select * from data_lockdown")) 
df_us_lockdown <- as.data.frame(df_us_lockdown)

df_us_health <- tbl(my_db, sql("select * from data_health")) 
df_us_health <- as.data.frame(df_us_health)

df_us_testing <- tbl(my_db, sql("select * from data_testing")) 
df_us_testing <- as.data.frame(df_us_testing)

plot_us.cases <- data.us.long %>% filter(state == "US") %>%
  ggplot(aes(x = date, y = count)) +
  geom_area(aes(fill=type), alpha=0.5) +
#  labs(title = paste0("Cumulative cases in US : ", min.date.txt.us, '-', max.date.txt.us, " (Stack, Log Scale)")) +
  scale_y_continuous(trans='log10') +
  scale_fill_manual(values=c('red', 'black'))+
  ylab("") +
  theme(axis.title = element_text(size=8))
#View(us.total.long )
plot_us.newconf <- us.total.long %>%
  filter(type %in% c("new.confirmed","new.deaths")) %>%
  ggplot(aes(x = date, y = count, color = type)) + 
  geom_line() + 
  #labs(title = paste0("Daily confirmed cases in US : ", min.date.txt.us, '-', max.date.txt.us)) +
  xlab("Date") +
  ylab("Confirmed cases")

gly.plot_us.cases <- ggplotly(plot_us.cases)

Transformation introduced infinite values in continuous y-axis

gly.plot_us.cases <- gly.plot_us.cases %>% 
  layout(title = paste0("<b>Cumulative cases in US : ", min.date.txt.us, '-', max.date.txt.us, " (Stack, Log Scale)"), 
         font=list(size = 10))
gly.plot_us.newconf <- ggplotly(plot_us.newconf) %>% 
  layout(title = paste0("<b>Daily confirmed cases in US : ", min.date.txt.us, '-', max.date.txt.us), 
         font=list(size = 10))

gly.plot_us.cases  

gly.plot_us.newconf

NA

data.us %<>% mutate(new.confirmed = ifelse(date == day1, NA, confirmed - lag(confirmed, n=1)),
                 new.deaths = ifelse(date == day1, NA, deaths - lag(deaths, n=1)))

data.us %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths))
View(data.us)

#data.us.daily <- data.us %>% filter(state == "US")
#View(data.us.daily)

data.us.pop <- df_us_pop %>% select(State, Population) %>%
  rename("state" = "State") 
#data.us.pop

data.us.gender <- df_us_gender %>% select(State, Male, Female) %>%
  rename("state" = "State") 
data.us.gender

data.us.latest <- data.us %>%
  filter(date == max.date.us) %>% 
  merge(data.us.pop, by = "state", all.x = T) %>%
  merge(data.us.gender, by = "state", all.x = T)
#View(data.us.latest)

data.us.latest$Population[data.us.latest$state == "US"] <- sum(data.us.pop$Population)
data.us.latest %<>% mutate(ranking = dense_rank(desc(confirmed)),
                           confirmed.rate = (100 * confirmed / Population) %>% round(2),
                           death.rate = (100 * deaths / confirmed) %>% round(2),
                           Male.confirmed = (Male * confirmed) %>% round(0),
                           Female.confirmed = Female * confirmed %>% round(0),
                           Male.deaths = Male * deaths,
                           Female.deaths = Female * deaths) %>%
  arrange(ranking) 

top.us <- data.us.latest[,1]
top.us

 [1] "US"                       "California"               "Texas"                    "Florida"                 
 [5] "Illinois"                 "New York"                 "Ohio"                     "Georgia"                 
 [9] "Pennsylvania"             "Tennessee"                "Michigan"                 "Wisconsin"               
[13] "North Carolina"           "Indiana"                  "Arizona"                  "New Jersey"              
[17] "Minnesota"                "Missouri"                 "Massachusetts"            "Alabama"                 
[21] "Virginia"                 "Colorado"                 "Louisiana"                "South Carolina"          
[25] "Iowa"                     "Oklahoma"                 "Maryland"                 "Utah"                    
[29] "Kentucky"                 "Washington"               "Kansas"                   "Nevada"                  
[33] "Arkansas"                 "Mississippi"              "Connecticut"              "Nebraska"                
[37] "Idaho"                    "New Mexico"               "Oregon"                   "Puerto Rico"             
[41] "South Dakota"             "North Dakota"             "Rhode Island"             "Montana"                 
[45] "West Virginia"            "Delaware"                 "Alaska"                   "Wyoming"                 
[49] "New Hampshire"            "District of Columbia"     "Maine"                    "Hawaii"                  
[53] "Guam"                     "Vermont"                  "Virgin Islands"           "Northern Mariana Islands"

#View(top.us)

data.us.latest.show <- data.us.latest %>% select(state,date,confirmed,deaths, confirmed.rate, death.rate)
#View(data.us.latest.show)

# List of top 20 state
k <- 20
data.us.top <- data.us.latest %>%
  filter(ranking <= k+1) %>% 
  arrange(ranking) 
#View(data.us.top)

us.state.top <- data.us.top %>% pull(state) %>% as.character()
us.state.top  %>% setdiff('US') %>% print()

 [1] "California"     "Texas"          "Florida"        "Illinois"       "New York"       "Ohio"           "Georgia"       
 [8] "Pennsylvania"   "Tennessee"      "Michigan"       "Wisconsin"      "North Carolina" "Indiana"        "Arizona"       
[15] "New Jersey"     "Minnesota"      "Missouri"       "Massachusetts"  "Alabama"        "Virginia"      

# confirmed rate & death rate of top 20 state
g.rate <- data.us.latest %>% filter(state %in% us.state.top & state != "US") %>%
  select(state, confirmed.rate, death.rate, ranking) %>%
  gather(key = Type, value = Percent, -c(state, ranking)) %>%
  ggplot(aes(x=reorder(state, -desc(ranking)), y=Percent, fill = Percent)) +
  geom_bar(stat='identity') +
  scale_fill_gradient(low = "#ebbc62", high = "#b42006") +
  geom_text(aes(label=Percent, y=Percent), size=3, vjust=0) +
  xlab('') + ylab('') +
  labs(title=paste0('Confirmed Rate & Death Rate of Top 20 State in US')) +
  #scale_fill_continuous(name='State', labels=aes(Percent)) +
  theme(legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size=13),
        axis.text=element_text(size=8),
        axis.text.x=element_text(angle=45, hjust=1)) +
  facet_wrap(~Type, ncol=1, scales='free_y') 

g.rate

us.confirmed.num <- data.us.top$confirmed[data.us.top$state == "US"] %>% as.numeric()
us.confirmed.num

[1] 18706036

data.us.top %<>% mutate(confirmed.per.us = (confirmed * 100 / us.confirmed.num) %>% round(1))
data.us.top

g.us.top1 <- data.us.top %>%
  filter(state != "US") %>%
  select(state, confirmed, confirmed.per.us, ranking) %>%
  gather(key = Type, value = count, -c(state, ranking, confirmed.per.us)) %>%
  arrange(ranking) %>%
  
  ggplot(aes(x = reorder(state, ranking), y = count, fill = count)) + 
  geom_bar(stat = "identity") +
  #labs(title = "20 state in US with most confirmed cases") +
  scale_color_gradient(low = "#93DBFF", high = "#FF7771") +
  xlab("") + 
  ylab("Confirmed Cases") +
  geom_text(aes(label=paste0(confirmed.per.us, "%")), size=3, vjust=-0.5) +
  theme(axis.text.x = element_text(angle = 90,vjust = 0.5))+
  theme(
    axis.line = element_blank(),
    axis.ticks = element_blank(),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_blank(),
    panel.border = element_blank(),
    panel.background = element_blank(),
    #legend.position = "none"
  )

g.us.top1

gly.us.top1 <- ggplotly(g.us.top1)
gly.us.top1

data.us.latest.corr <- df_us %>%filter(date==max.date.us)
#View(data.us.latest.corr)

data.us.latest.corr %<>% mutate(ranking = dense_rank(desc(confirmed))) %>% arrange(ranking)
top.us <- data.us.latest.corr[,2]
#top.us

# List of top 20 state
k <- 19
data.us.latest.top <- data.us.latest.corr %>%
    filter(ranking <= k+1) %>% 
    arrange(ranking) 
#View(data.us.latest.top)
us.state.top <- data.us.latest.top %>% pull(state) %>% as.character()
us.state.top  %>% setdiff('US') %>% print()

 [1] "California"     "Texas"          "Florida"        "Illinois"       "New York"       "Ohio"           "Georgia"       
 [8] "Pennsylvania"   "Tennessee"      "Michigan"       "Wisconsin"      "North Carolina" "Indiana"        "Arizona"       
[15] "New Jersey"     "Minnesota"      "Missouri"       "Massachusetts"  "Alabama"        "Virginia"      

#View(us.state.top)

#gender in us
df_us_gender <- tbl(my_db, sql("select * from data_gender"))
df_us_gender <- as.data.frame(df_us_gender)
df_us_gender <- select(df_us_gender,c("State","Male","Female"))
df_us_gender <- rename(df_us_gender,"state"="State")
df_us_gender

#population in us
df_us_pop <- tbl(my_db, sql("select * from data_population"))
df_us_pop <- as.data.frame(df_us_pop)
df_us_pop <- select(df_us_pop,c("State","Population"))
df_us_pop <- rename(df_us_pop,"state"="State")
df_us_pop

#lockdown in us
df_us_lockdown <- tbl(my_db, sql("select * from data_lockdown"))
df_us_lockdown <- as.data.frame(df_us_lockdown)
df_us_lockdown <- select(df_us_lockdown,c("State","Day lockdown"))
df_us_lockdown <- rename(df_us_lockdown,"state"="State")
df_us_lockdown

#GDP in us
df_us_gdp <- tbl(my_db, sql("select * from us_gdp"))
df_us_gdp  <- as.data.frame(df_us_gdp )
df_us_gdp  <- select(df_us_gdp ,c("State","GDPs"))
df_us_gdp  <- rename(df_us_gdp ,"state"="State")
df_us_gdp 

#homeless in us
df_us_homeless <- tbl(my_db, sql("select * from us_homeless"))
df_us_homeless <- as.data.frame(df_us_homeless)
df_us_homeless <- select(df_us_homeless,c("State","Homeless"))
df_us_homeless <- rename(df_us_homeless,"state"="State")
df_us_homeless

#View(df_us)

#merge
mergcountry = function(data1,data2){
  data <- merge(x = data1, y = data2, by = "state", all.x = TRUE) 
  return(data)
}

data.top.state <- data.us.latest.top %>% filter(state != "US") %>%
  select(state, confirmed, deaths)
#View(data.top.state)

df_Allus <- mergcountry(data.top.state, df_us_gender)

df_Allus <- mergcountry(df_Allus, df_us_pop) 

df_Allus <- mergcountry(df_Allus, df_us_lockdown) 

df_Allus <- mergcountry(df_Allus, df_us_gdp)

df_Allus <- mergcountry(df_Allus, df_us_homeless)

#View(df_Allus)

index <- is.na(df_Allus)
df_Allus[index] <- 0
#View(df_Allus)

normalize = function(data){
  #return ((data - min(data,na.rm = TRUE))/(max(data,na.rm = TRUE) - min(data,na.rm = TRUE)))
  z <- scale(data);
  tanh(z/2)
}
Allus = as.data.frame(apply(df_Allus[,2:9],2,normalize))
corr_dataUS <- Allus 

#View(corr_dataUS)

Allus$state <- c(df_Allus$state)
#View(Allus)

corr_dataUS <- rename(corr_dataUS ,"Daylockdown" = "Day lockdown")

Allus_plot <- select(Allus,"state","confirmed","deaths","Male","Female","Population","Day lockdown","GDPs","Homeless")
Allus_plot %<>% gather(key=type, value=count, -c(state))
level_order <- factor(Allus_plot$type, 
                      level = c("confirmed","deaths","Male","Female","Population","Day lockdown","GDPs","Homeless"))

Allus_Heat <- Allus[,c(ncol(Allus),1:(ncol(Allus)-1))]
rownames(Allus_Heat) <- Allus_Heat[,1]
Allus_Heat[,1] <- NULL
heatmap.2(as.matrix(Allus_Heat),
                    scale="none", 
                    col = colorRampPalette(c("#6D9EC1","white","#E46726"))(n = 200),
                    margins=c(10,6),trace="column")

heatmapus <- ggplot(data = Allus_plot, aes(x=state, y=level_order, fill=count)) + 
  geom_tile() +
  scale_fill_gradient(low = "pink", high = "blue") +
  xlab("") +
  ylab("") +
  theme_bw() +
  theme(axis.text.x = element_text(angle = 90,vjust = 1))+
  theme(
    axis.line = element_blank(),
    axis.ticks = element_blank(),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_blank(),
    panel.border = element_blank(),
    panel.background = element_blank(),
    #legend.position = "none"
  ) +labs(title='The heatmap of COVID-19 infections in US')

heatmapus

#correlation
corr_dataUS %<>% select(c(confirmed,deaths,Male,Female,Population,Daylockdown,GDPs,Homeless))
head(corr_dataUS)

cor(corr_dataUS)

               confirmed       deaths         Male       Female   Population Daylockdown        GDPs    Homeless
confirmed    1.000000000  0.792654639  0.105367061 -0.005945092  0.959941301  -0.2058259  0.91047827  0.79161590
deaths       0.792654639  1.000000000  0.008041947  0.017335533  0.852691131  -0.3797468  0.88497061  0.79642656
Male         0.105367061  0.008041947  1.000000000 -0.877329594 -0.009961687  -0.2898630 -0.02437981 -0.08015552
Female      -0.005945092  0.017335533 -0.877329594  1.000000000  0.069101784   0.3629328  0.02235864  0.15148878
Population   0.959941301  0.852691131 -0.009961687  0.069101784  1.000000000  -0.2155126  0.97052821  0.86317801
Daylockdown -0.205825865 -0.379746786 -0.289863040  0.362932815 -0.215512639   1.0000000 -0.36113782 -0.35735597
GDPs         0.910478267  0.884970609 -0.024379811  0.022358644  0.970528214  -0.3611378  1.00000000  0.89961070
Homeless     0.791615898  0.796426563 -0.080155515  0.151488777  0.863178014  -0.3573560  0.89961070  1.00000000

ggcorrplot(cor(corr_dataUS),hc.order = TRUE,
           outline.color = "white",
           colors = c("#6D9EC1","white","#E46726"),
           lab = TRUE)

NA
NA
NA

#Covid_Thailand
df_thai <- tbl(my_db, sql("select * from covid19_thailand"))
df_thai <- as.data.frame(df_thai)
#View(df_thai)

#clean Covid_Thailand
dates.th <- df_thai[,2]%>% mdy()
range(dates.th)

[1] "2020-01-12" "2021-01-15"

min.date.th <- min(dates.th)
max.date.th <- max(dates.th)
min.date.txt.th <- min.date.th %>% format('%d %b %Y')
max.date.txt.th <- max.date.th %>% format('%d %b %Y')

df_thai$announce_date <- mdy(df_thai$announce_date)
df_thai$notification_date <- mdy(df_thai$notification_date)
df_thai

df_thai <- df_thai %>% select(!No.) %>% select(!notification_date) %>% 
  group_by(announce_date)
df_thai

View(df_thai)

# Total confirmed cases in Thailand
data.thai.count <- df_thai %>%
  select(announce_date) %>%
  summarise(confirmed = n()) %>% as.data.frame()
View(data.thai.count)
data.thai.count$cumulative_confirmed <- cumsum(data.thai.count[, 2])

data.thai.count <- data.thai.count %>% rename(new.confirmed=confirmed) %>% rename(confirmed=cumulative_confirmed)
thai.total.long <- data.thai.count %>% 
  gather(key = type, value = count, -c(announce_date))
thai.total.long

plot_thailand.conf <- thai.total.long %>%
  filter(type %in% c("new.confirmed")) %>%
  ggplot(aes(x = announce_date, y = count, color = type)) + 
  geom_line() + 
  xlab("Date") +
  ylab("cases")

gly.plot_thai.cases <- ggplotly(plot_thailand.conf)
gly.plot_thai.cases <- gly.plot_thai.cases %>% 
  layout(title = paste0("<b>Daily Thai Confirmed Cases in Thailand : Jan 2020 - Jan 2021"), 
         font=list(size = 10))

gly.plot_thai.cases

plot_thailand.cumulative <- thai.total.long %>% 
  filter(type %in% c("confirmed")) %>%
  ggplot(aes(x = announce_date, y = count)) +
  geom_area(aes(fill=type), alpha=0.5) +

  scale_y_continuous(trans='log10') +
  scale_fill_manual(values=c('red'))+
  ylab("") +
  theme(axis.title = element_text(size=8))

gly.plot_thai.cumulative <- ggplotly(plot_thailand.cumulative)
gly.plot_thai.cumulative <- gly.plot_thai.cumulative %>% 
  layout(title = paste0("<b>Cumulative Cases in Thailand : Jan 2020 - Jan 2021 (Stack,Log scale)"), 
         font=list(size = 10))

gly.plot_thai.cumulative

## Thai Confirmed Cases (Jan 2020 - Jan 2021
plot1 <- ggplot(data.thai.count, aes(x=announce_date, y=confirmed)) +
  geom_point() + geom_smooth() +
  xlab(" ") + ylab("Count") + labs(title='Thai Cumulative Confirmed Cases (Jan 2020 - Jan 2021)') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot2 <- ggplot(data.thai.count, aes(x=announce_date, y=new.confirmed)) +
  geom_point() + geom_smooth() +
    xlab(" ") + ylab("Count")+ labs(title='Thai Confirmed Cases (Jan 2020 - Jan 2021 log scale)') +
   theme(axis.text.x=element_text(angle=45, hjust=1))+scale_y_continuous(trans='log10')
## show two plots side by side
grid.arrange(plot1, plot2, ncol=1)

# Confirmed cases divided by sex (gender)
df_thai$sex[df_thai$sex == ""] <- "Unknown"
data.thai.gender <- df_thai %>%
  group_by(sex) %>%
  summarise(count = n()) %>%
  mutate(percent = (count / sum(count) * 100) %>% round(2)) %>%
  filter(percent>1)%>%
  #mutate(pos = cumsum(percent) - 0.5*percent) %>%
  arrange(desc(percent))

data.thai.gender

data.thai.gender$sex <- factor(data.thai.gender$sex, levels = as.character(data.thai.gender$sex))
data.thai.gender$sex

[1] Male   Female
Levels: Male Female

g.th.gender <- data.thai.gender %>% 
  ggplot(aes(x = "", y = percent, fill = sex)) +
  geom_bar(stat = "identity", width = 1) +
  coord_polar("y") +
  theme_void() +
  labs(title='Gender of Thai Confirmed Cases (Jan 2020 - Jan 2021)')+
  geom_text(aes(label = paste0(percent, "%")), color = "white", size = 5, position = position_stack(vjust = 0.5)) +
  guides(fill = guide_legend(reverse = TRUE)) 
g.th.gender

# Confirmed cases divided by province_of_onset
data.thai.onset <- df_thai %>%
    group_by(province_of_onset) %>%
    summarise(count = n()) %>%
    arrange(desc(count))%>%
    rename(onset="count")%>%
    rename(province="province_of_onset")
data.thai.onset$province[data.thai.onset$province == ""] <- "Unknown"
data.thai.onset

# Confirmed cases divided by province_of_isolation 
data.thai.isolation <- df_thai %>%
    group_by(province_of_isolation) %>%
    summarise(count = n()) %>%
    arrange(desc(count))%>%
    rename(isolation ="count")%>%
    rename(province="province_of_isolation")

data.thai.isolation$province[data.thai.isolation$province == ""] <- "Unknown"
data.thai.isolation

data.province <- merge(x = data.thai.onset, y = data.thai.isolation, by = "province", all.x = TRUE) 
data.province <- data.province%>%filter(onset>125)
data.province

data.province.long <- data.province %>%
    gather(key=type, value=count, -c(province))
data.province.long

province.thai <- ggplot(data.province.long,aes(x=province,y=count))+
    geom_bar(stat = "identity",position = "dodge",aes(fill=type))+
    labs(title = "Province of onset and Province of isolation in Thailand")+
    theme(legend.title=element_blank(),
                  #legend.position='none',
                  plot.title=element_text(size=12),
                  axis.text=element_text(size=10),
                  axis.text.x=element_text(angle=45, hjust=1))+ xlab('') + ylab('Count')
gly.province.thai <- ggplotly(province.thai)
gly.province.thai

# Confirmed cases divided by risk
data.thai.risk <- df_thai %>%
  group_by(risk) %>%
  summarise(count = n()) %>%
  arrange(desc(count))
data.thai.risk

# Confirmed cases divided by risk
data.thai.risk <- df_thai %>%
  group_by(risk) %>%
  summarise(count = n()) %>%
  mutate(percent = (count / sum(count) * 100) %>% round(2)) %>%
  filter(percent>4) %>%
  arrange(desc(percent)) 
data.thai.risk

data.thai.risk$risk[data.thai.risk$risk == "C"] <- "Close contact with the patient"
data.thai.risk$risk[data.thai.risk$risk == "CGustDr Samut SaGGAF"] <- "Cluster Samut Sakhon"
data.thai.risk$risk[data.thai.risk$risk == "F"] <- "State Quarantine"
data.thai.risk$risk[data.thai.risk$risk == "G"] <- "Go to a crowded place"
data.thai.risk$risk[data.thai.risk$risk == "A"] <- "People travelling from abroad"
data.thai.risk$risk[data.thai.risk$risk == "UFGFAwF"] <- "Unknown"
data.thai.risk$risk[data.thai.risk$risk == "D"] <- "Career at risk"
data.thai.risk$risk[data.thai.risk$risk == "CGustDr RayAFg"] <- "Cluster Rayong"
data.thai.risk$risk[data.thai.risk$risk == "CGustDr Pattaya CasCFA"] <- "Cluster Pattaya Casino"
data.thai.risk$risk[data.thai.risk$risk == "CGustDr AFg TGAFg"] <- "Cluster Ang Thong"
data.thai.risk

data.thai.risk$risk <- factor(data.thai.risk$risk, levels = as.character(data.thai.risk$risk))
data.thai.risk$risk

[1] Close contact with the patient Cluster Samut Sakhon           State Quarantine              
[4] Go to a crowded place          People travelling from abroad  Unknown                       
[7] Career at risk                 Cluster Rayong                
8 Levels: Close contact with the patient Cluster Samut Sakhon State Quarantine ... Cluster Rayong

g.th.risk <- data.thai.risk %>% 
  ggplot(aes(x = "", y = percent, fill = risk)) +
  geom_bar(stat = "identity", width = 0.5) +
  coord_polar("y") +
  theme_void() +
  labs(title='Risk of Thai Confirmed Cases(Jan 2020 - Jan 2021)')+
  geom_text(aes(label = paste0(percent, "%")), color = "Black", size = 3, position = position_stack(vjust = 0.5)) +
  guides(fill = guide_legend(reverse = TRUE)) 
g.th.risk

# Confirmed cases divided by age
data.thai.age <- df_thai %>%
  group_by(age,sex) %>% 
  filter(age != 0)%>%
  summarise(count = n()) %>%
  arrange(desc(count))

`summarise()` has grouped output by 'age'. You can override using the `.groups` argument.

data.thai.age

data.thai.age <- data.thai.age# %>% filter(sex != "Unknown")
ggplot(data.thai.age,aes(x=age,y=count,fill=sex))+geom_bar(stat = "identity")+
      labs(title='Age of Thai Confirmed Cases (Stack)')+guides(fill=guide_legend(reverse = T))

# Confirmed cases divided by nationality
data.thai.nationality <- df_thai

data.thai.nationality$nationality[data.thai.nationality$nationality == "????????"] <- "Unknown"
data.thai.nationality$nationality[data.thai.nationality$nationality == ""] <- "Unknown"
data.thai.nationality$nationality[data.thai.nationality$nationality == "Burma"] <- "Myanmar"

data.thai.nationality <- data.thai.nationality %>%
  group_by(nationality) %>%
  summarise(count = n()) %>%
  filter(count > 11)%>%
  arrange(desc(count))

data.thai.nationality

data.thai.nationality$count[data.thai.nationality$nationality == "Unknown"]

[1] 118

ggplot(data.thai.nationality, aes(x=reorder(nationality, count),y=count,fill = count))+ 
  geom_bar(stat = "identity",show.legend = F)+
    ggtitle('Nationality of Confirmed Cases in Thailand (log scale)')+
    theme(plot.title = element_text(hjust = 0.5))+
    theme(axis.text = element_text(size = 10),
            axis.title = element_text(size = 10),
            plot.title = element_text(size = 11))+
    xlab("")+ylab("Number of Nationality")+
   scale_y_continuous(trans='log10')+coord_flip()+scale_fill_gradient(low="blue",high = "red")